import { In, QueryBuilder } from "typeorm"
import { AppDataSource } from "./data-source"
import { User } from "./entity/User"

AppDataSource.initialize().then(async () => {

    const D = AppDataSource.manager.getRepository(User);

    //插入或修改单条数据
    const user = new User()
    user.id = 1  //若指定id数据存在,就是更新
    user.firstName = "zjh"
    user.lastName = "NewKing"
    user.age = 25
    await AppDataSource.manager.save(user)


    //批量插入和修改数据
    await AppDataSource.manager.save(User, [
        // { firstName: 'ccc', lastName: 'ccc', age: 21},
        // { firstName: 'ddd', lastName: 'ddd', age: 22},
        // { firstName: 'eee', lastName: 'eee', age: 25}
        //{ id: 3 ,firstName: 'eee', lastName: 'eee', age: 25}
    ]);

    //查询多条数据
    const userAll = await AppDataSource.manager.find(User,{
        select: { firstName:true, age:true},
        where: { id: In([1,7])},
        order: { firstName: 'asc' }
    })
    //简易写法
    const userAll2 = await D.find({
        select: { firstName:true, age:true},
        where: { id: In([1,7])},
        order: { firstName: 'desc' }
    });
    console.log("FindAll: ", userAll)
    console.log("FindAll2: ", userAll2)

    //条件查询?好像和上面没区别
    const userWhere = await D.findBy({
        age: 25
    })
    console.log("FindBy: ", userWhere);

    //查询单条数据(LIMIT 1)
    const userOne = await D.findOne({
        where: { id: 5 }
    })
    console.log("FindOne: ", userOne)

    //条件查询同时返回数量
    const [users,count] = await D.findAndCountBy({
        age: 25
    })
    console.log("findAndCountBy: ", users,count);

    //查询失败抛出异常
    // try {
    //     const userFail = await AppDataSource.manager.findOneOrFail(User, {
    //         where: { id: 666 }
    //     });
    //     console.log(userFail);
    // }catch(e) {
    //     console.log(e);
    //     console.log('没找到该用户');
    // }


    //可以直接执行sql语句
    const userQuery = await D.query('select * from user where age in(?, ?)', [1,5]);
    console.log("Query: ", userQuery);

    //但复杂 sql 语句不会直接写，而是会用 query builder：
    const QueryBuilder = await AppDataSource.manager.createQueryBuilder();
    const userBuilder = await QueryBuilder.select("user") 
    .from(User, "user") 
    .where("user.age = :age", { age: 22 }) 
    .getOne();
    console.log(userBuilder);

         
   //删除
   await AppDataSource.manager.delete(User, 2);
   await AppDataSource.manager.delete(User, [3,4]);

  

    //事务
    /* await AppDataSource.manager.transaction(async manager => {
        await manager.save(User, {
            firstName: 'eee',
            lastName: 'eee',
            age: 20
        });
    }); */


}).catch(error => console.log(error))
